/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package de.tor.tribes.reportserver.util;

import de.tor.tribes.types.ZebraReport;
import de.tor.tribes.types.ZebraReportGroup;
import de.tor.tribes.types.ZebraUser;
import java.math.BigInteger;
import java.util.Date;
import java.util.List;

/**
 *
 * @author Torridity
 */
public class ReportFilterHelper {

    public static String getCountReportQuery(ZebraUser pUser,
            Date pFrom,
            Date pTo,
            String pAttacker,
            String pSource,
            String pDefender,
            String pTarget,
            List<ZebraReport.REPORT_COLOR> pIgnoredColors,
            boolean pAcceptanceDecrease,
            boolean pJustOwn) {
        StringBuilder q = new StringBuilder();

        q.append("SELECT COUNT(*) from ReportAccessGrants g, Reports r WHERE g.report_id=r.id AND g.user_userid='").
                append(pUser.getId()).
                append("' AND  (r.sent>='").
                append(pFrom.getTime()).
                append("' AND r.sent<='").
                append(pTo.getTime()).append("')");
        if (pAttacker != null) {
            q.append(" AND r.attacker LIKE '").append(pAttacker).append("%'");
        }
        if (pSource != null) {
            q.append(" AND r.source LIKE '").append(pSource).append("%'");
        }
        if (pDefender != null) {
            q.append(" AND r.defender LIKE '").append(pDefender).append("%'");
        }
        if (pTarget != null) {
            q.append(" AND r.target LIKE '").append(pTarget).append("%'");
        }

        if (pIgnoredColors != null && !pIgnoredColors.isEmpty()) {
            q.append(" AND (");
            for (ZebraReport.REPORT_COLOR color : pIgnoredColors) {
                q.append("r.color!='").append(color.ordinal()).append("' AND ");
            }
            //just for easy ending
            q.append("r.color!='666')");
        }

        if (pAcceptanceDecrease) {
            q.append(" AND r.acceptbefore!=r.acceptafter");
        }

        if (pJustOwn) {
            q.append(" AND g.owner='TRUE'");
        }
        return q.toString();
    }

    public static String getCountReportQueryByGroup(ZebraUser pUser,
            ZebraReportGroup pGroup,
            Date pFrom,
            Date pTo,
            String pAttacker,
            String pSource,
            String pDefender,
            String pTarget,
            List<ZebraReport.REPORT_COLOR> pIgnoredColors,
            boolean pAcceptanceDecrease,
            boolean pJustOwn) {
        StringBuilder q = new StringBuilder();

        q.append("SELECT COUNT(*) from ReportAccessGrants g, Reports r, ReportGroupMapping m WHERE m.grant_id=g.id AND m.group_id='").
                append(pGroup.getId()).
                append("' AND g.report_id=r.id AND g.user_userid='").
                append(pUser.getId()).
                append("' AND  (r.sent>='").
                append(pFrom.getTime()).
                append("' AND r.sent<='").
                append(pTo.getTime()).append("')");
        if (pAttacker != null) {
            q.append(" AND r.attacker LIKE '").append(pAttacker).append("%'");
        }
        if (pSource != null) {
            q.append(" AND r.source LIKE '").append(pSource).append("%'");
        }
        if (pDefender != null) {
            q.append(" AND r.defender LIKE '").append(pDefender).append("%'");
        }
        if (pTarget != null) {
            q.append(" AND r.target LIKE '").append(pTarget).append("%'");
        }

        if (pIgnoredColors != null && !pIgnoredColors.isEmpty()) {
            q.append(" AND (");
            for (ZebraReport.REPORT_COLOR color : pIgnoredColors) {
                q.append("r.color!='").append(color.ordinal()).append("' AND ");
            }
            //just for easy ending
            q.append("r.color!='666')");
        }

        if (pAcceptanceDecrease) {
            q.append(" AND r.acceptbefore!=r.acceptafter");
        }

        if (pJustOwn) {
            q.append(" AND g.owner='TRUE'");
        }
        return q.toString();

    }

    public static String getFindReportsQuery(ZebraUser pUser,
            Date pFrom,
            Date pTo,
            String pAttacker,
            String pSource,
            String pDefender,
            String pTarget,
            List<ZebraReport.REPORT_COLOR> pIgnoredColors,
            boolean pAcceptanceDecrease,
            boolean pJustOwn) {
        StringBuilder q = new StringBuilder();

        q.append("SELECT g.* from ReportAccessGrants g, Reports r WHERE g.user_userid='").
                append(pUser.getId()).
                append("'  AND g.report_id=r.id AND (sent>='").
                append(pFrom.getTime()).
                append("' AND sent<='").
                append(pTo.getTime()).append("')");

        if (pAttacker != null) {
            q.append(" AND attacker LIKE '").append(pAttacker).append("%'");
        }
        if (pSource != null) {
            q.append(" AND source LIKE '").append(pSource).append("%'");
        }
        if (pDefender != null) {
            q.append(" AND defender LIKE '").append(pDefender).append("%'");
        }
        if (pTarget != null) {
            q.append(" AND target LIKE '").append(pTarget).append("%'");
        }

        if (pIgnoredColors != null && !pIgnoredColors.isEmpty()) {
            q.append(" AND (");
            for (ZebraReport.REPORT_COLOR color : pIgnoredColors) {
                q.append("color!='").append(color.ordinal()).append("' AND ");
            }
            //just for easy ending
            q.append("color!='666')");
        }

        if (pAcceptanceDecrease) {
            q.append(" AND acceptbefore!=acceptafter");
        }

        if (pJustOwn) {
            q.append(" AND g.owner='TRUE'");
        }
        return q.toString();
    }

    public static String getFindReportsQuery(ZebraUser pUser,
            ZebraReportGroup pGroup,
            Date pFrom,
            Date pTo,
            String pAttacker,
            String pSource,
            String pDefender,
            String pTarget,
            List<ZebraReport.REPORT_COLOR> pIgnoredColors,
            boolean pAcceptanceDecrease,
            boolean pJustOwn) {
        StringBuilder q = new StringBuilder();

        q.append("SELECT * from ReportAccessGrants g, Reports r, ReportGroupMapping m WHERE m.grant_id=g.id AND m.group_id='").
                append(pGroup.getId()).
                append("' AND g.report_id=r.id AND g.user_userid='").
                append(pUser.getId()).
                append("' AND (r.sent>='").
                append(pFrom.getTime()).
                append("' AND r.sent<='").
                append(pTo.getTime()).append("')");

        if (pAttacker != null) {
            q.append(" AND r.attacker LIKE '").append(pAttacker).append("%'");
        }
        if (pSource != null) {
            q.append(" AND r.source LIKE '").append(pSource).append("%'");
        }
        if (pDefender != null) {
            q.append(" AND r.defender LIKE '").append(pDefender).append("%'");
        }
        if (pTarget != null) {
            q.append(" AND target LIKE '").append(pTarget).append("%'");
        }

        if (pIgnoredColors != null && !pIgnoredColors.isEmpty()) {
            q.append(" AND (");
            for (ZebraReport.REPORT_COLOR color : pIgnoredColors) {
                q.append("r.color!='").append(color.ordinal()).append("' AND ");
            }
            //just for easy ending
            q.append("r.color!='666')");
        }

        if (pAcceptanceDecrease) {
            q.append(" AND r.acceptbefore!=r.acceptafter");
        }

        if (pJustOwn) {
            q.append(" AND g.owner='TRUE'");
        }
        return q.toString();
    }
}
